
set hive.exec.dynamic.partition=true; -- 动态分区
set hive.exec.dynamic.partition.mode=nonstrict;
set hive.exec.max.dynamic.partitions=500; -- 每天生成 60 个分区
set hive.exec.max.dynamic.partitions.pernode=500; -- 每天生成 60 个分区

with tab_complaint as (
select duty_network_code     as network_code  
       ,duty_network_name    as network_name
       ,duty_network_type    as network_type   
       ,duty_proxy_code      as proxy_code
       ,duty_proxy_name      as proxy_name
       ,if(order_source_name in ('桃花岛跨境','桃花岛跨境（逆）','桃花岛','紫金山','逍遥峰','极地湾','麦田圈','苏宁','有赞','七星潭'),order_source_name,'其他')  as ordersource_name       
       ,count(1)            complaint_cnt
  from jms_dm.dm_waybill_customer_complaint_detail_new_dt 
 where dt = '{{ execution_date | cst_ds }}' and duty_network_type = '6'
 group by duty_network_code
       ,duty_network_name
       ,duty_network_type
       ,duty_proxy_code
       ,duty_proxy_name
       ,if(order_source_name in ('桃花岛跨境','桃花岛跨境（逆）','桃花岛','紫金山','逍遥峰','极地湾','麦田圈','苏宁','有赞','七星潭'),order_source_name,'其他')     
),

---2.应签收量 24点准点签收量

tab_sign_punctuality as (
select final_sign_network_code   as network_code 
       ,final_sign_network_name  as network_name
       ,'6'                      as network_type 
       ,end_agent_code           as proxy_code
       ,end_agent_name           as proxy_name
       ,if(order_source_name in ('桃花岛跨境','桃花岛跨境（逆）','桃花岛','紫金山','逍遥峰','极地湾','麦田圈','苏宁','有赞','七星潭'),order_source_name,'其他')  as ordersource_name              
       ,sum(all_sign_24)         as all_sign_24
       ,sum(need_sign_count)     as need_sign_count
  from jms_dm.dm_terminal_sign_punctuality_rate_dt
 where dt = '{{ execution_date | cst_ds }}'
 group by final_sign_network_code
       ,final_sign_network_name
       ,end_agent_code
       ,end_agent_name
       ,if(order_source_name in ('桃花岛跨境','桃花岛跨境（逆）','桃花岛','紫金山','逍遥峰','极地湾','麦田圈','苏宁','有赞','七星潭'),order_source_name,'其他')             
),
---3.
tab_intime_taking as (
select receive_network_code      as network_code 
       ,receive_network_name  as network_name
       ,'6'                   as network_type 
       ,receive_proxy_code    as proxy_code
       ,receive_proxy         as proxy_name
       ,order_source          as ordersource_name              
       ,nvl(sum(hand_over_in_time_num),0)  as hand_over_in_time_num
       ,nvl(sum(should_hand_over_num),0)   as should_hand_over_num
  from jms_dm.dm_waybill_collect_intime_taking_agg
 where dt = date_sub('{{ execution_date | cst_ds }}',1)
 group by receive_network_code     
          ,receive_network_name 
          ,'6'                  
          ,receive_proxy_code   
          ,receive_proxy        
          ,order_source
)

insert overwrite table jms_dm.dm_robot_merge_kpi_network_sum_dt partition (dt)
select '{{ execution_date | cst_ds }}'  as stat_date
       ,COALESCE(a.network_code,b.network_code,c.network_code)               as network_code
       ,COALESCE(a.network_name,b.network_name,c.network_name)               as network_name
       ,COALESCE(a.network_type,b.network_type,c.network_type)               as network_type       
       ,COALESCE(a.proxy_code,b.proxy_code,c.proxy_code)                     as proxy_area_code
       ,COALESCE(a.proxy_name,b.proxy_name,c.proxy_name)                     as proxy_area_name
       ,COALESCE(a.ordersource_name,b.ordersource_name,c.ordersource_name)   as ordersource_name
       ,nvl(sum(need_sign_count),0)                               as need_sign_cnt
       ,nvl(sum(all_sign_24),0)                                   as all_sign_24
       ,nvl(sum(complaint_cnt),0)                                 as complaint_cnt
       ,nvl(sum(hand_over_in_time_num),0)                         as hand_over_in_time_num
       ,nvl(sum(should_hand_over_num),0)                          as should_hand_over_num
       ,'{{ execution_date | cst_ds }}'                                       as dt
  from tab_sign_punctuality   a
  full join tab_complaint     b 
    on a.network_code = b.network_code
   and a.ordersource_name = b.ordersource_name
  full join tab_intime_taking c
    on a.network_code = c.network_code
   and a.ordersource_name = c.ordersource_name
 group by COALESCE(a.network_code,b.network_code,c.network_code)            
         ,COALESCE(a.network_name,b.network_name,c.network_name)            
         ,COALESCE(a.network_type,b.network_type,c.network_type)            
         ,COALESCE(a.proxy_code,b.proxy_code,c.proxy_code)                  
         ,COALESCE(a.proxy_name,b.proxy_name,c.proxy_name)                  
         ,COALESCE(a.ordersource_name,b.ordersource_name,c.ordersource_name)
distribute by 1
;